create database HelpDesk
go
use HelpDesk
go
create table Category
(
   CategoryID int identity(1,1) primary key,
   CategoryName nvarchar(200),
   CategoryPriority nvarchar(200) 
)

go
create table Department
(
   DepartmentID int identity(1,1) primary key,
   DepartmentName nvarchar(200) 
)
go
create table Members 
(
   MembersID int identity(1,1) primary key,
   DepartmentID int,
   MembersName nvarchar(200),
   MembersUser nvarchar(200),
   MembersGender nvarchar(50),
   MembersAddress nvarchar(300),
   MembersPhone nvarchar(200),
   MembersPassword nvarchar(200),
   MembersPosition nvarchar(200),
   MembersRole int,
   constraint FK_Members_Department foreign key (DepartmentID) references Department(DepartmentID) on delete cascade
)

go
create table Complaint
(
  ComplaintID int identity(1,1) primary key,
  CategoryID int ,
  DepartmentID int,
  MembersID int,
  complaintTechnical int,
  ComplaintTitle nvarchar(500),
  ComplaintContent nvarchar(max),
  ComplaintCreateDate datetime default getdate(),
  ComplaintCloseDate datetime,
  ComplaintStatus int,
  ComplaintPriority nvarchar(200),
  constraint FK_Complaint_Category foreign key (CategoryID) references Category(CategoryID) on delete cascade ,
  constraint FK_Complaint_Department foreign key (DepartmentID) references Department(DepartmentID) on delete cascade ,
  constraint FK_Complaint_Members foreign key (MembersID) references Members(MembersID),
  constraint FK_Complaint_Technician foreign key (complaintTechnical) references Members(MembersID)
  
)   
create table Solution
(
  SolutionID int identity(1,1) primary key,
  MembersID int,
  ComplaintID int,
  SolutionContent nvarchar(max),
  SolutionCreateDate datetime default getdate(),
  constraint FK_Solution_Members foreign key (MembersID) references Members(MembersID) on delete cascade ,
  constraint FK_Solution_Complaint foreign key (ComplaintID) references Complaint(ComplaintID) 
)

go
create table Articles
(
  ArticlesId int identity(1,1) primary key,
  MembersID int,
  ArticlesTitle nvarchar(300),
  ArticlesContent nvarchar(max),
  ArticlesCreateDate datetime default getdate(),
  constraint FK_Articles_Members foreign key (MembersID) references Members(MembersID) on delete cascade  
  
)

